using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.IO;

namespace ODX.Core
{
    public interface IProviderFactory
    {
        DbConnection CreateConnection();
        DbDataAdapter CreateDataAdapter();
        DbCommandBuilder CreateCommandBuilder();
    }

    /// <summary>
    /// Data provider to be used with ADO.NET providers.
    /// </summary>
    public class DbDataProvider : MarshalByRefObject, ISqlDataProvider
    {
        private IProviderFactory factory;
        private readonly string connStr;
        //private DataSet ds;
        private NullsPosition nullsPosition = NullsPosition.Undefined;

#if !PocketPC
        /// <summary>
        /// Creates a new data provider.
        /// </summary>
        /// <param name="dataFactory">ADO.NET data provider factory.</param>
        /// <param name="connStr">Connection string to the underlying database.</param>
        public DbDataProvider(DbProviderFactory dataFactory, string connStr)
        {
            factory = new OdxProviderFactory(dataFactory);
            this.connStr = connStr;
        }
#endif

        public DbDataProvider(IProviderFactory dataFactory, string connStr)
        {
            factory = dataFactory;
            this.connStr = connStr;
        }

        private DbConnection Open()
        {
            DbConnection connection = factory.CreateConnection();
            connection.ConnectionString = connStr;
            connection.Open();
            return connection;
        }

        private DataSet LoadFromTable(string sql, string tableName)
        {
            return LoadFromTable(sql, tableName, null, 0, null);
        }

        private DataSet LoadFromTable(string sql, string tableName, DataSet dataSet, int count, object[] values)
        {
            using ( DbConnection connection = Open() )
            {
                return LoadFromTable(connection, null, sql, tableName, count, dataSet, values);
            }
        }

        private DataSet LoadFromTable(DbConnection c, DbTransaction t, string sql, string tableName, int count, DataSet dataSet, object[] values)
        {
            DbCommand command = c.CreateCommand();
            command.Transaction = t;
            if (values != null && sql != null )
            {
                sql = FilterParser.Parse(sql, delegate(int n)
                                                  {
                                                      string pName =
                                                          GetParameterPlaceholder(c, t, tableName, n);
                                                      DbParameter prm = command.CreateParameter();
                                                      prm.Value = values[n];
                                                      prm.ParameterName = pName;
                                                      prm.Direction = ParameterDirection.Input;
                                                      command.Parameters.Add(prm);
                                                      return pName;
                                                  });
            }

            command.CommandText = sql;
            DataSet copy = dataSet ?? new DataSet();
            DbDataAdapter adapter = factory.CreateDataAdapter();
            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            adapter.SelectCommand = command;
            if (count > 0)
                adapter.Fill(copy, 0, count, tableName);
            else
                adapter.Fill(copy, tableName);

            copy.AcceptChanges();
            return copy;
        }

        /// <summary>
        /// Selects records from underlying database using specified filter sort order.
        /// </summary>
        /// <param name="tableName">Table name in data base to be queried.</param>
        /// <param name="filter">Filter expression to be applied to the table. '?' marks are accepted as parameters placeholders</param>
        /// <param name="sort">List of sort fields.</param>
        /// <param name="count">Nubler of records to be queried.</param>
        /// <param name="parameters">Parameters to be used instead '?' in filter expression.</param>
        /// <returns>DataSet contained queried data and optionally prefetched records.</returns>
        public DataSet Select(string tableName, string filter, string sort, int count, params object[] parameters)
        {
            string sql = "SELECT * FROM " + tableName;
            if ( filter != null )
                sql += " WHERE " + filter;
            if ( sort != null )
                sql += " ORDER BY " + sort;
            return LoadFromTable(sql, tableName, null, count, parameters);
        }


        /// <summary>
        /// Queries single record from a data source.
        /// </summary>
        /// <param name="tableName">Table name in data source to be queried.</param>
        /// <param name="ID">ID of a record to be returned.</param>
        /// <returns>DataSet contained the requested record and optionally prefetched records.</returns>
        public DataSet GetRecord(string tableName, string ID)
        {
            return LoadFromTable(string.Format("SELECT * FROM " + tableName + " WHERE ID = '{0}'", ID), tableName);
        }

        /// <summary>
        /// Returns complete list of child records for the specified parent record.
        /// </summary>
        /// <param name="childTableName">Name of child table.</param>
        /// <param name="childFK">Name of FK field in child table.</param>
        /// <param name="parentID">ID of parent record.</param>
        /// <returns>DataSet contained child records and optionally prefetched records.</returns>
        public DataSet GetChildren(string childTableName, string childFK, string parentID)
        {
            string sql = string.Format("SELECT child.* FROM {0} child WHERE child.{1} = '{2}'",
                childTableName,
                childFK,
                parentID);

            return LoadFromTable(sql, childTableName);
        }

        /// <summary>
        /// Returns records linked N:M to the specified parent record.
        /// </summary>
        /// <param name="linkTableName">Link table name.</param>
        /// <param name="childTableName">Name of table to query child records</param>
        /// <param name="parentFK">Name of fielf in link table that is FK to the parent table.</param>
        /// <param name="childFK">Name of fielf in link table that is FK to the child table.</param>
        /// <param name="parentID">ID of parent record.</param>
        /// <returns>DataSet contained child records and optionally prefetched records</returns>
        public DataSet GetAssoc(string linkTableName, string childTableName, string parentFK, string childFK, string parentID)
        {
            string sql = string.Format("SELECT child.* FROM {0} child JOIN {1} link ON child.ID = link.{2} WHERE link.{3} = '{4}'",
                childTableName,
                linkTableName,
                childFK,
                parentFK,
                parentID);

            DataSet rowset = LoadFromTable(sql, childTableName);

            sql = string.Format("SELECT link.* FROM {0} link WHERE link.{1} = '{2}'",
                linkTableName,
                parentFK,
                parentID);

            return LoadFromTable(sql, linkTableName, rowset, 0, null);
        }

        /// <summary>
        /// Selects records from a data source using specified SQL statement.
        /// </summary>
        /// <param name="selectSql">SQL SELECT statement.</param>
        /// <param name="tableName">Table name to put results into.</param>
        /// <param name="count">Number of records to be retrieved from the data source.</param>
        /// <param name="parameters">Parameters to be used instead '?' marks in sql statement.</param>
        /// <returns>DataSet contained queried data and optionally prefetchd records.</returns>
        public DataSet SelectSql(string selectSql, string tableName, int count, params object[] parameters)
        {
            return LoadFromTable(selectSql, tableName, null, count, parameters);

        }

        private static string CreateFldList(DataTable dt)
        {
            List<string> columns = new List<string>();
            foreach ( DataColumn dc in dt.Columns )
                columns.Add(dc.ColumnName);

            return string.Join(", ", columns.ToArray());
        }

        private static string GetID(DataRow row)
        {
            if (row.HasVersion(DataRowVersion.Original))
                return row["ID", DataRowVersion.Original].ToString();
            else
                return row["ID", DataRowVersion.Current].ToString();
        }

        private void SaveRow(DataRow row, DbTransaction t, DbConnection c, bool overwriteConcurrentChanges)
        {
            string ID = GetID(row);
            DataRow dbRow = TestRow(row, t, c);

            if (!overwriteConcurrentChanges)
            {
                bool conflict = false;
                if (row.RowState == DataRowState.Added ^ dbRow == null)
                    conflict = true;
                else if (dbRow != null)
                    conflict = GetConflict(dbRow, row);

                if (conflict)
                    throw new DBConcurrencyException("Concurrent access violation", null, new DataRow[] { row });
            }

            if (row.RowState != DataRowState.Deleted)
            {
                foreach (Constraint ct in row.Table.Constraints)
                {
                    UniqueConstraint uct = ct as UniqueConstraint;
                    if (uct != null && !uct.IsPrimaryKey)
                    {
                        DataRow ucr = GetUniqueRow(c, t, uct, row);
                        if (ucr != null)
                        {
                            if (overwriteConcurrentChanges)
                                DeleteRow(ucr["ID"].ToString(), ucr.Table.TableName, t, c);
                            else
                                throw new DBConcurrencyException("Unique constraint violation", null,
                                                                 new DataRow[] {row});
                        }
                    }
                }
            }


            if (dbRow == null)
                InsertRow(row, t, c);
            else if (row.RowState == DataRowState.Deleted)
                DeleteRow(ID, row.Table.TableName, t, c);
            else
                UpdateRow(row, t, c);
        }

        private DataRow GetUniqueRow(DbConnection c, DbTransaction t, UniqueConstraint uct, DataRow row)
        {
            string tableName = row.Table.TableName;
            object[] parameters;
            string filter = SmartDataSetMerger.GetFilter(uct, row.Table, row, out parameters);
            string sql = string.Format("SELECT {0} FROM {1} WHERE {2}",
                                       CreateFldList(row.Table),
                                       tableName,
                                       filter);

            DataSet res = LoadFromTable(c, t, sql, tableName, 1, null, parameters);
            if (res.Tables[tableName].Rows.Count > 0)
                return res.Tables[tableName].Rows[0];
            return null;

        }

        private static bool GetConflict(DataRow dbRow, DataRow row)
        {
            if (!dbRow.Table.Columns.Contains(SpecialColumns.RowVersion))
            {
                foreach (DataColumn dc in row.Table.Columns)
                    if (!dbRow[dc.ColumnName, DataRowVersion.Original].Equals(
                             row[dc.ColumnName, DataRowVersion.Original]))
                        return true;
            }
            else
            {
                if (!dbRow[SpecialColumns.RowVersion, DataRowVersion.Original].Equals(
                         row[SpecialColumns.RowVersion, DataRowVersion.Original]))
                    return true;
            }
            return false;
        }

        private static void DeleteRow(string id, string tableName, DbTransaction t, DbConnection c)
        {
            DbCommand cmd = c.CreateCommand();
            cmd.Transaction = t;
            cmd.CommandText = string.Format("DELETE FROM {0} WHERE ID = '{1}'", tableName, id);
            cmd.ExecuteNonQuery();
        }

        private void UpdateRow(DataRow row, DbTransaction t, DbConnection c)
        {
            string tableName = row.Table.TableName;
            DbCommand cmd = c.CreateCommand();
            cmd.Transaction = t;

            List<string> items = new List<string>();
            foreach ( DataColumn dc in row.Table.Columns )
            {
                if ( dc == dc.Table.PrimaryKey[0] )
                    continue;

                string pName = GetParameterPlaceholder(t.Connection, t, tableName, items.Count);
                items.Add(dc.ColumnName + " = " + pName);
                DbParameter prm = cmd.CreateParameter();
                prm.Direction = ParameterDirection.Input;
                prm.Value = row[dc];
                prm.ParameterName = pName;
                cmd.Parameters.Add(prm);
            }

            cmd.CommandText = string.Format("UPDATE {0} SET {1} WHERE ID = '{2}'",
                                            tableName,
                                            string.Join(", ", items.ToArray()),
                                            GetID(row));
            cmd.ExecuteNonQuery();
        }

        private void InsertRow(DataRow row, DbTransaction t, DbConnection c)
        {
            string tableName = row.Table.TableName;
            DbCommand cmd = c.CreateCommand();
            cmd.Transaction = t;

            List<string> prms = new List<string>();
            foreach ( DataColumn dc in row.Table.Columns )
            {
                string pName = GetParameterPlaceholder(c, t, tableName, prms.Count);
                DbParameter prm = cmd.CreateParameter();
                prm.Direction = ParameterDirection.Input;
                prm.Value = row[dc];
                prm.ParameterName = pName;
                prms.Add(pName);
                cmd.Parameters.Add(prm);
            }

            cmd.CommandText = string.Format("INSERT INTO {0}({1}) VALUES({2})",
                                            tableName,
                                            CreateFldList(row.Table),
                                            string.Join(", ", prms.ToArray()));
            cmd.ExecuteNonQuery();
        }

        private DataRow TestRow(DataRow row, DbTransaction t, DbConnection c)
        {
            string tableName = row.Table.TableName;
            string ID = GetID(row);

            DbCommand cmd = c.CreateCommand();
            cmd.Transaction = t;

            cmd.CommandText = string.Format("SELECT {0} from {1} where ID = '{2}'",
                                            CreateFldList(row.Table),
                                            row.Table.TableName,
                                            ID);
            DbDataAdapter adapter = factory.CreateDataAdapter();
            adapter.SelectCommand = cmd;
            DataSet DbSet = new DataSet();
            adapter.Fill(DbSet, tableName);
            if ( DbSet.Tables[tableName].Rows.Count == 0 )
                return null;
            return DbSet.Tables[tableName].Rows[0];
        }

        /// <summary>
        /// Saves DataSet to the underlying database
        /// </summary>
        /// <param name="changes">DataSet, typically contained changes made by user or algorithms.</param>
        /// <param name="saveMode">Concurrent changes processing rules.</param>
        public void Save(DataSet changes, SaveMode saveMode)
        {
            using (DbConnection connection = Open())
            {
                DbTransaction t = null;
                try
                {
                    bool overwriteConcurrentChanges = saveMode == SaveMode.Complete ||
                                                      saveMode == SaveMode.Overwrite;

                    t = connection.BeginTransaction();
                    ArrayList tables = DataTablesOrderer.Sort(changes);
                    tables.Reverse();
                    foreach (DataTable dt in tables)
                    {
                        foreach (DataRow dr in dt.Select(null, null, DataViewRowState.Deleted))
                            SaveRow(dr, t, connection, overwriteConcurrentChanges);
                    }
                    tables.Reverse();
                    foreach (DataTable dt in tables)
                    {
                        foreach (DataRow dr in dt.Select(null, null, DataViewRowState.Added))
                            SaveRow(dr, t, connection, overwriteConcurrentChanges);
                        foreach (DataRow dr in dt.Select(null, null,
                                                         overwriteConcurrentChanges
                                                             ? DataViewRowState.ModifiedCurrent |
                                                               DataViewRowState.Unchanged
                                                             : DataViewRowState.ModifiedCurrent))
                            SaveRow(dr, t, connection, overwriteConcurrentChanges);
                    }
                    t.Commit();
                }
                catch (Exception)
                {
                    if (t != null)
                        t.Rollback();
                    throw;
                }
            }
        }

        /// <summary>
        /// Defines positions of nulls in ordered data. 
        /// </summary>
        public NullsPosition NullsPosition
        {
            get { return nullsPosition; }
            set { nullsPosition = value; }
        }

    
        private static Dictionary<string, string> cmdParametersPlaceholders = new Dictionary<string, string>();

        private string GetParameterPlaceholder(DbConnection c, DbTransaction t, string tableName, int n)
        {
            string prms;
            if ( ! cmdParametersPlaceholders.TryGetValue(c.ConnectionString, out prms) )
            {
                DbCommandBuilder cb = factory.CreateCommandBuilder();
                DbDataAdapter adapter = factory.CreateDataAdapter();
                DbCommand cmd = c.CreateCommand();
                cmd.Transaction = t;
                cmd.CommandText = "SELECT * FROM " + tableName;
                adapter.SelectCommand = cmd;
                cb.DataAdapter = adapter;
                DbCommand insert = cb.GetInsertCommand();
                string sql = insert.CommandText;
                int open = sql.LastIndexOf("(");
                int close = sql.LastIndexOf(")");
                prms = sql.Substring(open + 1, close - open - 1).Split(',')[0].Trim();
                cmdParametersPlaceholders[c.ConnectionString] = prms;
            }

            if (prms.IndexOf('0') > 0)
                return prms.Replace("0", n.ToString());
            if (prms.IndexOf('1') > 0)
                return prms.Replace("1", n.ToString());
            return prms;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="ds"></param>
        public void PrepareDatabase(DataSet ds)
        {
            PrepareDatabase(ds, Sql92TypeMapper);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="mapper"></param>
        public void PrepareDatabase(DataSet ds, DbTypeMapper mapper)
        {
            DataSet oldDs = GetSchemaDataSet();

            if (oldDs != null)
                UpgradeDatabase(ds, oldDs, mapper);
            else
                CreateDatabase(ds, mapper);

            WriteSchemaDataSet(ds);
        }

        private void WriteSchemaDataSet(DataSet ds)
        {
            ds.WriteXmlSchema("db_schema.xml");
        }

        private DataSet GetSchemaDataSet()
        {
            if ( File.Exists("db_schema.xml") )
            {
                DataSet ds = new DataSet();
                ds.ReadXmlSchema("db_schema.xml");
                return ds;
            }
            return null;

        }

        private void UpgradeDatabase(DataSet ds, DataSet oldDs, DbTypeMapper mapper)
        {

            ArrayList tables = DataTablesOrderer.Sort(ds);

            using (DbConnection conn = Open())
            {
                foreach (DataTable dt in tables)
                {
                    DataTable old_dt = oldDs.Tables[dt.TableName];
                    if (!oldDs.Tables.Contains(dt.TableName))
                        CreateTable(dt, mapper, conn);
                    else
                        foreach (DataColumn dc in dt.Columns)
                            if (!old_dt.Columns.Contains(dc.ColumnName))
                                AddColumn(dc, mapper, conn);

                    foreach ( Constraint c in dt.Constraints )
                        if ( !old_dt.Constraints.Contains(c.ConstraintName) )
                            CreateConstraint(c, conn);
                }
            }
        }

        private static void AddColumn(DataColumn dc, DbTypeMapper mapper, DbConnection conn)
        {
            int maxLength = (int)dc.ExtendedProperties["dbMaxLength"];
            int scale = (int)dc.ExtendedProperties["dbScale"];

            string dbType = (string) dc.ExtendedProperties["dbType"];
            dbType = dbType ?? mapper(dc.DataType, maxLength, scale);

            string sql = string.Format("ALTER TABLE {0} ADD {1} {2}",
                dc.Table.TableName,
                dc.ColumnName,
                dbType);

            DbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// Creates SQL-92 database.
        /// </summary>
        /// <param name="ds">database schema descriprion to be created.</param>
        public void CreateDatabase(DataSet ds)
        {
            CreateDatabase(ds, Sql92TypeMapper);
        }

        /// <summary>
        /// Creates Database tables
        /// </summary>
        /// <param name="ds">Schema to create</param>
        /// <param name="mapper">Delegate to map DB types to .net types.</param>
        public void CreateDatabase(DataSet ds, DbTypeMapper mapper)
        {
            using ( DbConnection conn = Open() )
            {
                ArrayList tables = DataTablesOrderer.Sort(ds);
                foreach (DataTable dt in tables)
                    CreateTable(dt, mapper, conn);

                foreach (DataTable dt in tables)
                    foreach ( Constraint c in dt.Constraints )
                        CreateConstraint(c, conn);

            }
        }

        private static void CreateConstraint(Constraint c, DbConnection conn)
        {
            if (c is UniqueConstraint)
                CreateUniqueConstraint(c as UniqueConstraint, conn);
            else if (c is ForeignKeyConstraint)
                CreateForeignConstraint(c as ForeignKeyConstraint, conn);
            else
                throw new OdxException("Can't ctreate constraint " + c.GetType().FullName);
        }

        private static void CreateTable(DataTable dt, DbTypeMapper mapper, DbConnection conn)
        {
            List<string> columns = new List<string>();
            foreach (DataColumn dc in dt.Columns)
                columns.Add(string.Format("{0} {1}", dc.ColumnName, GetDBType(dc, mapper)));

            string sql = "CREATE TABLE " + dt.TableName + "(" + string.Join(", ", columns.ToArray()) + ")";

            DbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
        }

        private static void CreateForeignConstraint(ForeignKeyConstraint constraint, DbConnection conn)
        {
            List<string> columns = new List<string>();
            foreach (DataColumn dc in constraint.Columns)
                columns.Add(dc.ColumnName);

            List<string> relatedColumns = new List<string>();
            foreach (DataColumn dc in constraint.RelatedColumns)
                relatedColumns.Add(dc.ColumnName);

            string sql = string.Format("ALTER TABLE {0} ADD CONSTRAINT {1} FOREIGN KEY ({2}) REFERENCES {3}({4})",
                                       constraint.Table.TableName,
                                       constraint.ConstraintName,
                                       string.Join(", ", columns.ToArray()),
                                       constraint.RelatedTable.TableName,
                                       string.Join(", ", relatedColumns.ToArray()));


            DbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
        }

        private static void CreateUniqueConstraint(UniqueConstraint constraint, DbConnection conn)
        {
            List<string> columns = new List<string>();
            foreach (DataColumn dc in constraint.Columns)
                columns.Add(dc.ColumnName);

            string constraintType = constraint.IsPrimaryKey ? "PRIMARY KEY" : "UNIQUE";
            string sql = string.Format("ALTER TABLE {0} ADD CONSTRAINT {1} {2} ({3})",
                                       constraint.Table.TableName,
                                       constraint.ConstraintName,
                                       constraintType,
                                       string.Join(", ", columns.ToArray()));


            DbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
        }

        private static string GetDBType(DataColumn dc, DbTypeMapper mapper)
        {
            if (dc.ExtendedProperties.ContainsKey("dbType"))
                return dc.ExtendedProperties["dbType"].ToString();

            if (mapper != null)
            {
                int maxLength = (int) dc.ExtendedProperties["dbMaxLength"];
                int scale = (int) dc.ExtendedProperties["dbScale"];
                return mapper(dc.DataType, maxLength, scale);
            }

            throw new OdxException("Cant create column of type " + dc.DataType.FullName);
        }

        /// <summary>
        /// Maps netTypes to SQL-92 DB types
        /// </summary>
        /// <param name="netType">.net type.</param>
        /// <param name="maxLength">Maximum length for string and numeric data</param>
        /// <param name="scale">Sacle for the numeric data.</param>
        /// <returns>DB type.</returns>
        public static string Sql92TypeMapper(Type netType, int maxLength, int scale)
        {
            if (netType == typeof(string))
                return "CHARACTER VARYING(" + maxLength + ")";

            if (netType == typeof(int) || netType == typeof(uint) ||
                netType == typeof(long) || netType == typeof(ulong))
                return "INTEGER";

            if (netType == typeof(short) || netType == typeof(ushort) ||
                netType == typeof(byte))
                return "SMALLINT";

            if (netType == typeof(float))
                return "REAL";

            if (netType == typeof(double))
                return "DOUBLE PRECISION";

            if (netType == typeof(decimal))
                return "DECIMAL";

            if (netType == typeof(DateTime))
                return "DATE";

            throw new Exception("Can't deternine DB type");
        }
    }
    /// <summary>
    /// Maps .net type to DB type
    /// </summary>
    /// <param name="netType">.net type.</param>
    /// <param name="maxLength">Maximum length for string and numeric data</param>
    /// <param name="scale">Sacle for the numeric data.</param>
    /// <returns>DB type.</returns>
    public delegate string DbTypeMapper(Type netType, int maxLength, int scale);

}
